Skip to main content

Transaction Management

A transaction is a sequence of one or more SQL operations (INSERT, UPDATE, DELETE, SELECT) that form a single logical unit of work.

The goal of transaction management:

  • Ensure ACID properties (Atomicity, Consistency, Isolation, Durability).
  • Handle failures (crashes, power loss, errors).
  • Ensure data integrity across multiple operations.

Suppose we want to transfer $500 from Alice’s account to Bob’s account.

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 500 WHERE account_id = 1; -- Alice
UPDATE Accounts SET balance = balance + 500 WHERE account_id = 2; -- Bob

COMMIT;
  • If both statements succeed → transaction is committed (durable).
  • If one statement fails (e.g., crash after debit but before credit) → transaction is rolled back (atomic).
  • Constraints like “balance ≥ 0” enforce consistency.

Concurrency Control​

When multiple transactions run at the same time, problems can occur if they interact with the same data. Concurrency control ensures that isolation is preserved without sacrificing too much performance.

Problems Without Concurrency Control​

  1. Dirty Read – Reading uncommitted changes of another transaction. Example: T1 updates Alice’s balance but hasn’t committed; T2 reads it → later T1 rolls back → T2 read invalid data.

  2. Non-Repeatable Read – Reading the same row twice gives different results because another transaction updated it in between.

  3. Phantom Read – A transaction re-executes a query and sees new rows inserted by another transaction.

Solutions (Concurrency Control Mechanisms)​

  1. Locking (Pessimistic Concurrency Control)
    • Shared Lock (S-lock): For reading. Multiple readers allowed, but no writers.
    • Exclusive Lock (X-lock): For writing. Only one transaction can hold it.

Example:

BEGIN TRANSACTION;
SELECT balance FROM Accounts WHERE account_id = 1 FOR UPDATE;
-- prevents others from reading/writing until commit/rollback

  1. Timestamp Ordering (Optimistic Concurrency Control)

    • Transactions get timestamps; older ones get priority.
    • Detects conflicts at commit time.

  2. Multiversion Concurrency Control (MVCC) (used in PostgreSQL, Oracle, etc.)

    • Readers see a snapshot of the database at the start of their transaction.
    • Writers create new versions instead of blocking readers.
    • Prevents dirty reads and non-repeatable reads without heavy locking.

Relation to Integrity in Database Design​

  • Entity integrity (PKs), Referential integrity (FKs), Domain integrity (constraints) work at the schema level.
  • Transaction management & concurrency control ensure that these rules aren’t violated at runtime.

Example:

  • Referential integrity: Cannot delete a customer if orders exist (unless CASCADE).
  • If two transactions simultaneously try to delete and insert related records, locks/MVCC ensure the database remains consistent.